package com.cola.dao;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.alibaba.fastjson.TypeReference;
import com.cola.entity.Customer;
import com.cola.entity.JoinSaleActivity;
import com.cola.entity.order.Order;
import com.cola.entity.order.OrderDetail;
import com.cola.entity.shop.Goods;
import com.cola.entity.shop.SaleActivity;
import com.cola.entity.shop.ShopCarGoods;
import com.cola.utils.BasicRowProcessorFix;
import com.cola.utils.DBControl;
import com.cola.utils.DateUtils;
import com.cola.utils.SnowflakeIdWorker;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * Created by Auser on 2017/8/22.
 */
public class ShopDao extends BaseDao {
    private final Logger logger =  LoggerFactory.getLogger(getClass());

    private static ShopDao ShopDao = new ShopDao();

    private ShopDao(){}

    public static ShopDao getInstance(){
        return ShopDao;
    }

    /**
     * 功能：新增店铺
     * 参数：填入的店铺信息
     * 返回：i>0为true代表新增店铺成功，为false代表新增店铺失败
     * 作者：tony
     */
    public Boolean addShop(String customer_name ,String city ,String area, String address,
                           String customer_mobile, String customer_contact_name, String main_image,String images, String channel_type, String shop_size,BigDecimal lat,BigDecimal lng){
        PreparedStatement ps = null;
        Connection conn = null;
        String sql = "insert into cola_customer(customer_name, city, area, address, customer_mobile, customer_contact_name, is_bind , status, check_status,create_time, " +
                "customer_img, customer_imgs, channel_type_no, shop_size, customer_lng, customer_lat, " +
                "customer_system_no) VALUES (?,?,?,?,?,?,0,0,1,NOW(),?,?,?,?,?,?,?)";
        SnowflakeIdWorker idWorker = new SnowflakeIdWorker(1, 1);
        long customer_system_no = idWorker.nextId();
        try {
            conn = DBControl.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1,customer_name);
            ps.setString(2,city);
            ps.setString(3,area);
            ps.setString(4,address);
            ps.setString(5,customer_mobile);
            ps.setString(6,customer_contact_name);
            ps.setString(7,main_image);
            ps.setString(8, images);
            ps.setString(9,channel_type);
            ps.setString(10,shop_size);
            ps.setBigDecimal(11,lng);
            ps.setBigDecimal(12,lat);
            ps.setString(13,String.valueOf(customer_system_no));
            return ps.executeUpdate()>0;
        } catch (SQLException se) {
            logger.error("ShopDao的addShop方法出现异常！",se);
        }finally {
            DbUtils.closeQuietly(conn,ps,null);
        }

        return false;
    }

    /**
     * 功能：编辑店铺信息
     * 参数：需修改的店铺信息
     * 返回：i>0为true代表编辑成功，为false代表编辑失败
     * 作者：tony
     */
    public boolean editShop(Integer customer_id, String customer_name , String city, String area, String address,
                            String customer_mobile, String customer_contact_name, String main_image,String images, String shop_size,BigDecimal lat,BigDecimal lng){
        PreparedStatement ps = null;
        Connection conn = null;
        String sql="update cola_customer set customer_name=?, city=?, area=?, address=?, customer_mobile=?, customer_contact_name=?, customer_img=? ,customer_imgs=?, shop_size=?, customer_lat=?, customer_lng=?" +
                " where customer_id=? ";
        try {
            conn = DBControl.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1,customer_name);
            ps.setString(2,city);
            ps.setString(3,area);
            ps.setString(4,address);
            ps.setString(5,customer_mobile);
            ps.setString(6,customer_contact_name);
            ps.setString(7,main_image);
            ps.setString(8, images);
            ps.setString(9,shop_size);
            ps.setBigDecimal(10,lat);
            ps.setBigDecimal(11,lng);
            ps.setInt(12,customer_id);
            return ps.executeUpdate()>0;
        } catch (SQLException se) {
            logger.error("ShopDao的editShop方法出现异常！",se);
        }finally {
            DbUtils.closeQuietly(conn,ps,null);
        }

        return false;
    }

    /**
     * 功能：根据店铺编号获取店铺的经纬度
     * 参数：店铺编号
     * 返回：店铺的经纬度
     * 作者：tony
     */
    public Map<String, Object> getLngAndLatByCustomerId(Integer customerId){
        Connection conn=null;
        try {
            conn=DBControl.getConnection();
            String sql="select customer_lng, customer_lat, area from cola_customer where customer_id=? ";
            return getQueryRunner().query(conn,sql,new MapHandler(),customerId);
        } catch (SQLException se) {
            logger.error("根据店铺编号获取经纬度异常！",se);
        } finally {
            DbUtils.closeQuietly(conn);
        }
        return null;
    }


    /**
     * 根据手机号获取店铺信息
     * @param mobile
     * @return Map<String,Object>
     * @author Max
     * @since 2017/9/14
     */
    public Map<String,Object> getShopInfo(String mobile){
        Connection conn = null;
        try{
            conn = DBControl.getConnection();
            String sql = "select a.*,b.area_name as city_name ,c.area_name as area_name ,d.`name` as channel_name from cola_customer a " +
                    "LEFT JOIN system_area b on a.city = b.area_no  " +
                    "LEFT JOIN system_area c on a.area = c.area_no  " +
                    "LEFT JOIN system_dictionary d on a.channel_type_no = d.d_no where mobile =? ";
            return getQueryRunner().query(conn,sql,new MapHandler(new BasicRowProcessorFix()),mobile);

        }catch (Exception e){
            logger.error("根据手机号获取店铺信息异常！",e);
        }finally {
            DbUtils.closeQuietly(conn);
        }
        return null;
    }


    //====================================商品相关====================================
    
    /**
     * 获取全部物料列表
     * @param areaNo 区域编号
     * @param saleChannelNo 渠道编号
     * @return List<Goods> 物料列表
     * @author Max
     * @since 2017/9/14
     */
    public List<Goods> getGoodsList(String areaNo ,String saleChannelNo){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String date = DateUtils.getCurrentDateTime();
        List<Goods> list = new ArrayList<Goods>();
        Goods goods = new Goods();
        try{
            conn = DBControl.getConnection();
            String sql = "SELECT a.*,b.goods_price,b.sale_city_no,b.sale_area_no,b.sale_channel_no ,c.`name` as goods_specifications FROM `cola_goods` a INNER JOIN cola_goods_config b " +
                    "on a.goods_no = b.goods_no INNER JOIN system_dictionary c on a.goods_specifications_no = c.d_no where a.`check_status`=1 and b.`check_status`=1 and start_time<=?  " +
                    "and end_time>=? and a.`status`=1 and b.`status`=1 and b.sale_area_no =? and b.sale_channel_no=?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,date);
            ps.setString(2,date);
            ps.setString(3,areaNo);
            ps.setString(4,saleChannelNo);
            rs = ps.executeQuery();
            while (rs.next()){
                goods = new Goods();
                goods.setGoods_id(rs.getInt("goods_id"));
                goods.setGoods_no(rs.getString("goods_no"));
                goods.setGoods_name(rs.getString("goods_name"));
                goods.setGoods_price(rs.getBigDecimal("goods_price"));
                goods.setGoods_img(rs.getString("goods_img"));
                goods.setGoods_imgs(JSONObject.parseObject(rs.getString("goods_imgs"),new TypeReference<List<String>>(){}));
                goods.setGoods_brand_no(rs.getString("goods_brand_no"));
                goods.setGoods_type_no(rs.getString("goods_type_no"));
                goods.setGoods_specifications_no(rs.getString("goods_specifications_no"));
                goods.setDesc(rs.getString("desc"));
                goods.setSale_city_no(rs.getString("sale_city_no"));
                goods.setSale_area_no(rs.getString("sale_area_no"));
                goods.setSale_channel_no(rs.getString("sale_channel_no"));
                goods.setGoods_specifications(rs.getString("goods_specifications"));
                list.add(goods);
            }
            return list;
        }catch (Exception e){
            logger.error("获取全部物料列表异常！",e);
        }finally {
            DbUtils.closeQuietly(conn,ps,rs);
        }
        return null;
    }


    /**
     * 获取促销活动配置
     * @param areaNo 区域编号
     * @param saleChannelNo 渠道编号
     * @return List<SaleActivity> 促销列表
     * @author Max
     * @since 2017/9/14
     */
    public List<SaleActivity> getSaleActivityList(String areaNo,String saleChannelNo){
        Connection conn = null;
        try{
            conn = DBControl.getConnection();
            String sql = "SELECT a.*,sale_city_no,sale_area_no,sale_channel_no,giveaway_name FROM `cola_sale` a " +
                    "INNER JOIN cola_sale_detail b on a.sale_no = b.sale_no " +
                    "LEFT  JOIN cola_giveaway c on a.giveaway_no = c.giveaway_no " +
                    "where b.sale_area_no =? and sale_channel_no =?";
            return getQueryRunner().query(conn,sql,new BeanListHandler<SaleActivity>(SaleActivity.class),areaNo,saleChannelNo);

        }catch (Exception e){
            logger.error("获取促销活动配置异常！",e);
        }finally {
            DbUtils.closeQuietly(conn);
        }
        return null;
    }


    /**
     * 获取促销活动配置
     * @param saleNo 促销活动编号
     * @return List<SaleActivity> 促销列表
     * @author Max
     * @since 2017/9/14
     */
    public List<SaleActivity> getSaleActivityBySaleNo(String saleNo){
        Connection conn = null;
        try{
            conn = DBControl.getConnection();
            String sql = "SELECT a.*,sale_city_no,sale_area_no,sale_channel_no,giveaway_name FROM `cola_sale` a " +
                    "INNER JOIN cola_sale_detail b on a.sale_no = b.sale_no " +
                    "LEFT  JOIN cola_giveaway c on a.giveaway_no = c.giveaway_no " +
                    "where a.sale_no =?";
            return getQueryRunner().query(conn,sql,new BeanListHandler<>(SaleActivity.class),saleNo);

        }catch (Exception e){
            logger.error("获取促销活动配置异常！",e);
        }finally {
            DbUtils.closeQuietly(conn);
        }
        return null;
    }


    //===============================================================================


    //=====================================购物车====================================

    /**
     * 获取购物车商品列表
     * @param customerId 店铺id
     * @param areaNo 区域编号
     * @param saleChannelNo 渠道编号
     * @return List<ShopCarGoods> 购物车商品列表
     * @author Max
     * @since 2017/9/15
     */
    public List<ShopCarGoods> getShopCarGoodsList(Integer customerId,String areaNo,String saleChannelNo){
        Connection conn = null;
        try{
            conn = DBControl.getConnection();
            String sql = "SELECT a.num as goods_num, b.goods_name,b.goods_no,c.goods_price FROM `cola_shop_car` a " +
                    "INNER JOIN cola_goods b on a.goods_no = b.goods_no " +
                    "INNER JOIN cola_goods_config c on a.goods_no = c.goods_no " +
                    "where c.sale_area_no=? and c.sale_channel_no=? and a.customer_id =?";
            return getQueryRunner().query(conn,sql,new BeanListHandler<ShopCarGoods>(ShopCarGoods.class),areaNo,saleChannelNo,customerId);
        }catch (Exception e){
            logger.error("",e);
        }finally {
            DbUtils.closeQuietly(conn);
        }
        return null;
    }


    /**
     * 获取购物车商品列表
     * @param customerId 店铺id
     * @param areaNo 区域编号
     * @param saleChannelNo 渠道编号
     * @return List<ShopCarGoods> 购物车商品列表
     * @author Max
     * @since 2017/9/15
     */
    public List<ShopCarGoods> getShopCarGoodsDetailList(Integer customerId,String areaNo,String saleChannelNo){
        Connection conn = null;
        try{
            conn = DBControl.getConnection();
            String sql = "SELECT a.num as goods_num, b.goods_name,b.goods_img,b.goods_no,c.goods_price,d.name as goods_specifications,'GOODS' as goods_type FROM `cola_shop_car` a " +
                    "INNER JOIN cola_goods b on a.goods_no = b.goods_no " +
                    "INNER JOIN cola_goods_config c on a.goods_no = c.goods_no " +
                    "INNER JOIN system_dictionary d on b.goods_specifications_no = d.d_no " +
                    "where c.sale_area_no=? and c.sale_channel_no=? and a.customer_id =?";
            return getQueryRunner().query(conn,sql,new BeanListHandler<ShopCarGoods>(ShopCarGoods.class),areaNo,saleChannelNo,customerId);
        }catch (Exception e){
            logger.error("",e);
        }finally {
            DbUtils.closeQuietly(conn);
        }
        return null;
    }

    /**
     * 添加商品到购物车
     * @param customerId 店铺编号
     * @param goodsNo 商品编号
     * @param num 数量
     * @return true false
     * @author Max
     * @since 2017/9/15
     */
    public boolean addToShopCar(Integer customerId,String goodsNo,Integer num){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        boolean status = false;
        try{
            conn = DBControl.getConnection();
            conn.setAutoCommit(false);
            String sql = "select count(shop_car_id) from cola_shop_car where goods_no =? and customer_id =?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,goodsNo);
            ps.setInt(2,customerId);
            rs = ps.executeQuery();
            if(rs.next()){
                if(rs.getInt(1)>0){
                    String sql2 = "update cola_shop_car set num = num+? where goods_no=? and customer_id =?";
                    status= getQueryRunner().update(conn,sql2,num,goodsNo,customerId)>0;
                }else{
                    String sql2 = "insert into cola_shop_car(customer_id,goods_no,num,create_time) values(?,?,?,?)";
                    status= getQueryRunner().update(conn,sql2,customerId,goodsNo,num,DateUtils.getCurrentDateTime())>0;
                }
            }
            conn.commit();
            return status;
        }catch (Exception e){
            logger.error("添加商品到购物车异常！",e);
            if(conn!=null){
                try{
                    conn.rollback();
                }catch (Exception w){
                    logger.error("添加商品到购物车回滚异常!",e);
                }
            }
        }finally {
            DbUtils.closeQuietly(conn,ps,rs);
        }
        return false;
    }

    /**
     * 购物车删除商品
     * @param customerId 店铺编号
     * @param goodsNo 商品编号
     * @param num 数量
     * @return
     * @author Max
     * @since 2017/9/15
     */
    public boolean subFromShopCar(Integer customerId,String goodsNo,Integer num){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        boolean status = false;
        try{
            conn = DBControl.getConnection();
            conn.setAutoCommit(false);
            String sql = "select num from cola_shop_car where goods_no =? and customer_id =?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,goodsNo);
            ps.setInt(2,customerId);
            rs = ps.executeQuery();
            if(rs.next()){
                if(rs.getInt(1)>1){
                    String sql2 = "update cola_shop_car set num = num-? where goods_no=? and customer_id =?";
                    status= getQueryRunner().update(conn,sql2,num,goodsNo,customerId)>0;
                }else{
                    String sql2 = "delete from  cola_shop_car where goods_no=? and customer_id =?";
                    status= getQueryRunner().update(conn,sql2,goodsNo,customerId)>0;
                }
            }
            conn.commit();
            return status;
        }catch (Exception e){
            logger.error("购物车删除商品异常！",e);
            if(conn!=null){
                try{
                    conn.rollback();
                }catch (Exception w){
                    logger.error("购物车删除商品回滚异常!",e);
                }
            }
        }finally {
            DbUtils.closeQuietly(conn,ps,rs);
        }
        return false;
    }

    /**
     * 清空购物车
     * @param customerId
     * @return false true
     * @author Max
     * @since 2017/9/18
     */
    public boolean emptyShopCar(Integer customerId){
        Connection conn = null;
        try{
            conn = DBControl.getConnection();
            String sql = "delete from cola_shop_car where customer_id =?";
            getQueryRunner().update(conn,sql,customerId);
            return true;
        }catch (Exception e){
            logger.error("清空购物车异常！",e);
        }finally {
            DbUtils.closeQuietly(conn);
        }
        return false;
    }

    //===============================================================================


    //===================================订单相关=====================================

    /**
     * 下单
     * @param order
     * @return true;false
     * @author Max
     * @since 2017/9/16
     */
    public boolean createOrder(Order order,Integer customerId,String date,JoinSaleActivity saleActivity){
        Connection conn = null;
        try{
            conn = DBControl.getConnection();

            conn.setAutoCommit(false);

            //创建主订单
            boolean a = createMainOrder(conn,order,date);

            //创建订单详情
            boolean b = createOrderDetail(conn,order);

            //从购物车删除
            boolean c = deleteFromShopCar(conn,order,customerId);

            //从购物车删除
            boolean d = saveJoinActivity(conn,saleActivity,date);

            conn.commit();
            return a && b && c && d;
        }catch (Exception e){
            logger.error("创建订单异常！",e);
            if(conn!=null){
                try{
                    conn.rollback();
                }catch (Exception w){
                    logger.error("创建订单回滚异常！",w);
                }
            }
        }
        return false;
    }

    /**
     * 创建主订单
     * @param conn,order 订单
     * @return
     * @author Max
     * @since 2017/9/16
     */
    private boolean createMainOrder(Connection conn,Order order,String date) throws Exception {
        try{
            String sql = "insert into cola_order(order_no,customer_id,mep_no,city_no,city_name,area_no,area_name,address,order_price" +
                    ",goods_num,order_status,create_time,receiver,mobile,is_price_off,off_price,check_code,sale_no,order_price_final,goods_num_final,off_price_final)" +
                    " values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ";

            return getQueryRunner().update(conn,sql,order.getOrder_no(),order.getCustomer_id(),order.getMep_no(), order.getCity_no(),order.getCity_name()
                ,order.getArea_no(),order.getArea_name(),order.getAddress(), order.getOrder_price(),order.getGoods_num()
                    ,order.getOrder_status(),date,order.getReceiver(),order.getMobile(),order.getIs_price_off(),order.getOff_price(),order.getCheck_code()
                    ,order.getSale_no(),order.getOrder_price_final(),order.getGoods_num_final(),order.getOff_price_final())>0;
        }catch (Exception e){
            logger.error("创建主订单异常！",e);
            throw e;
        }
    }

    /**
     * 创建订单详情
     * @param conn,order
     * @return 
     * @author Max
     * @since 2017/9/16
     */
    private boolean createOrderDetail(Connection conn,Order order)throws Exception {
        try{
            String sql = "insert into cola_order_detail(type,customer_id,order_no,goods_no,goods_name,goods_img,goods_num,goods_unit_price,goods_price,goods_specifications,mep_no" +
                    ",status ,goods_num_final,goods_price_final)" +
                    " values(?,?,?,?,?,?,?,?,?,?,?,1,?,?)";
            List<OrderDetail> list = order.getOrder_details();
            Object[][] params= new Object[list.size()][13];
            for(int i=0; i<list.size(); i++){
                params[i][0] = list.get(i).getType();
                params[i][1] = list.get(i).getCustomer_id();
                params[i][2] = list.get(i).getOrder_no();
                params[i][3] = list.get(i).getGoods_no();
                params[i][4] = list.get(i).getGoods_name();
                params[i][5] = list.get(i).getGoods_img();
                params[i][6] = list.get(i).getGoods_num();
                params[i][7] = list.get(i).getGoods_unit_price();
                params[i][8] = list.get(i).getGoods_price();
                params[i][9] = list.get(i).getGoods_specifications();
                params[i][10] = list.get(i).getMep_no();
                params[i][11] = list.get(i).getGoods_num_final();
                params[i][12] = list.get(i).getGoods_price_final();
            }
            return getQueryRunner().batch(conn,sql,params).length>0;
        }catch (Exception e){
            logger.error("创建订单详情异常！",e);
            throw e;
        }
    }

    /**
     * 从购物车删除下单商品信息
     * @param conn 链接
     * @param order 订单
     * @param customerId 店铺id
     * @return order 订单
     * @author Max
     * @since 2017/9/17
     * @throws Exception
     */
    private boolean deleteFromShopCar(Connection conn, Order order, Integer customerId) throws Exception {
        try{
            String sql = "delete from cola_shop_car where customer_id=? and goods_no =?";
            List<OrderDetail> list = order.getOrder_details();
            Object[][] params= new Object[list.size()][2];
            for(int i=0; i<list.size(); i++){
                params[i][0] = customerId;
                params[i][1] = list.get(i).getGoods_no();
            }
            return getQueryRunner().batch(conn,sql,params).length>0;
        }catch (Exception e){
            logger.error("从购物车删除下单商品信息异常！");
            throw e;
        }
    }


    /**
     * 插入参与促销活动
     * @param conn 链接
     * @param activity 参与的促销活动信息
     * @param date 日期
     * @return true;false
     * @author Max
     * @since 2017/9/20
     * @throws Exception
     */
    private boolean saveJoinActivity(Connection conn,JoinSaleActivity activity,String date) throws Exception {
        try{
            String sql = "insert into cola_sale_join(sale_type,order_no,sale_json,create_time) values(?,?,?,?)";

            return getQueryRunner().update(conn,sql,activity.getSale_type(),activity.getOrder_no(),activity.getSale_json(),date)>0;

        }catch (Exception e){
            logger.error("插入参与促销活动异常！",e);
            throw  e;
        }
    }


    /**
     * 获取赠品信息
     * @param giveaway_no 赠品编号
     * @return ShopCarGoods
     * @author Max
     * @since 2017/9/16
     */
    public ShopCarGoods getGiveawayInfo(String giveaway_no){
        Connection conn = null;
        try{
            conn = DBControl.getConnection();
            String sql = "select giveaway_no as goods_no ,giveaway_name as goods_name, giveaway_img as goods_img ," +
                    "b.`name` as goods_specifications ,'GIVEAWAY' as goods_type " +
                    "from cola_giveaway a INNER JOIN system_dictionary b on a.giveaway_specifications_no = b.d_no " +
                    " where a.giveaway_no =?";
            return getQueryRunner().query(conn,sql,new BeanHandler<ShopCarGoods>(ShopCarGoods.class),giveaway_no);
        }catch (Exception e){
            logger.error("获取赠品信息异常！",e);
        }finally {
            DbUtils.closeQuietly(conn);
        }
        return null;
    }


    /**
     * 获取订单列表
     * @param customerId 店铺id
     * @author Max
     * @since 2017/9/17
     */
    public List<Order> getOrderList(Integer customerId){
        Connection conn = null;
        try{
            conn = DBControl.getConnection();
            String sql = "select * from cola_order where customer_id =? order by create_time desc";
            return getQueryRunner().query(conn,sql,new BeanListHandler<Order>(Order.class),customerId);
        }catch (Exception e){
            logger.error("获取全部订单列表异常！",e);
        }finally {
            DbUtils.closeQuietly(conn);
        }
        return null;
    }
    
    /**
     * 获取订单详情列表
     * @param customerId 店铺id
     * @return List<OrderDetail>
     * @author Max
     * @since 2017/9/17
     */
    public List<OrderDetail> getOrderDetailList(Integer customerId){
        Connection conn = null;
        try{
            conn = DBControl.getConnection();
            String sql = "select * from cola_order_detail where customer_id =? order by create_time desc";
            return getQueryRunner().query(conn,sql,new BeanListHandler<OrderDetail>(OrderDetail.class),customerId);
        }catch (Exception e){
            logger.error("获取全部订单列表异常！",e);
        }finally {
            DbUtils.closeQuietly(conn);
        }
        return null;
    }

    /**
     * 根据订单编号获取订单详情
     * @param orderNo 订单编号
     * @return
     * @author Max
     * @since 2017/9/17
     */
    public Order getOrderInfoByOrderNo(String orderNo){
        Connection conn = null;
        try{
            conn = DBControl.getConnection();
            String sql = "select * from cola_order where   order_no = ? ";
            return getQueryRunner().query(conn,sql,new BeanHandler<Order>(Order.class),orderNo);
        }catch (Exception e){
            logger.error("根据订单编号获取订单详情异常！",e);
        } finally {
            DbUtils.closeQuietly(conn);
        }
        return null;
    }


    /**
     * 根据订单编号获取订单宝贝列表
     * @param orderNo 订单编号
     * @return
     * @author Max
     * @since 2017/9/17
     */
    public List<OrderDetail> getOrderDetailInfoByOrderNo(String orderNo){
        Connection conn = null;
        try{
            conn = DBControl.getConnection();
            String sql = "select * from cola_order_detail where order_no = ? ";
            return getQueryRunner().query(conn,sql,new BeanListHandler<OrderDetail>(OrderDetail.class),orderNo);
        }catch (Exception e){
            logger.error("根据订单编号获取订单宝贝列表！",e);
        } finally {
            DbUtils.closeQuietly(conn);
        }
        return null;
    }


    //====================================个人中心===================================
    
    /**
     * 保存修改信息
     * @param customer 店铺信息
     * @return true,false
     * @author Max
     * @since 2017/9/18
     */
    public boolean editShopInfo(Customer customer){
        Connection conn = null;
        try{
            conn = DBControl.getConnection();
            String sql = "update cola_customer set customer_name =? ,city=?,area=?,address=? " +
                    ", customer_mobile=?,customer_contact_name=?,customer_img=?,channel_type_no=?" +
                    ",customer_lat=?,customer_lng=?,shop_size=? where customer_id=?";
            return getQueryRunner().update(conn,sql,customer.getCustomer_name(),customer.getCity(),customer.getArea(),customer.getAddress(),
                    customer.getCustomer_mobile(),customer.getCustomer_contact_name(),customer.getCustomer_img(),customer.getChannel_type_no()
                    ,customer.getCustomer_lat(),customer.getCustomer_lng(),customer.getShop_size(),customer.getCustomer_id())>0;
        }catch (Exception e){
            logger.error("保存修改店铺信息异常！",e);
        }finally {
            DbUtils.closeQuietly(conn);
        }
        return false;
    }

    /**
     * 保存修改地址信息
     * @param customer 店铺信息
     * @return true,false
     * @author Max
     * @since 2017/9/18
     */
    public boolean editShopAddressInfo(Customer customer){
        Connection conn = null;
        try{
            conn = DBControl.getConnection();
            String sql = "update cola_customer set city=?,area=?,address=? " +
                    ",customer_lat=?,customer_lng=? where customer_id=? ";
            return getQueryRunner().update(conn,sql,customer.getCity(),customer.getArea(),customer.getAddress()
                    ,customer.getCustomer_lat(),customer.getCustomer_lng(),customer.getCustomer_id())>0;
        }catch (Exception e){
            logger.error("保存修改地址信息异常！",e);
        }finally {
            DbUtils.closeQuietly(conn);
        }
        return false;
    }

    /**
     * 保存店铺信息
     * @param customer
     * @return true;false
     * @author Max
     * @since 2017/9/20
     */
    public boolean saveShopInfo(Customer customer){
        Connection conn = null;
        try{
            conn = DBControl.getConnection();
            String sql = "insert into cola_customer(customer_system_no,customer_name,channel_type_no,city,area,address,shop_size" +
                    ",customer_mobile,customer_contact_name,customer_wechat_nick,customer_open_id,customer_head_img_url,customer_lat," +
                    "customer_lng,customer_img,customer_imgs,is_bind,status,check_status,create_time) " +
                    "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,1,1,1,?)";

            return getQueryRunner().update(conn,sql,customer.getCustomer_system_no(),customer.getCustomer_name(),customer.getChannel_type_no(),customer.getCity()
                    ,customer.getArea(),customer.getAddress(),customer.getShop_size(),customer.getCustomer_mobile(), customer.getCustomer_contact_name()
                    ,customer.getCustomer_wechat_nick(),customer.getCustomer_open_id(), customer.getCustomer_head_img_url(), customer.getCustomer_lat()
                    , customer.getCustomer_lng(), customer.getCustomer_img(),customer.getCustomer_imgs(),DateUtils.getCurrentDateTime())>0;
        }catch (Exception e){
            logger.error("保存用户信息异常！",e);
        }finally {
            DbUtils.closeQuietly(conn);
        }
        return false;
    }

    //=========================================评价======================================

    /**
     * 配送评价
     * @param speed_score 速度评分
     * @param courier_score 配送评分
     * @param goods_score 商品完好度
     * @param evaluate_content 评价内容
     * @param orderNo 订单编号
     * @param mepNo 经销商编号
     * @param courierId 配送员Id
     * @return true false
     * @author Max
     * @since 2017/9/20
     */
    public boolean doEvaluate(Integer speed_score,Integer courier_score,Integer goods_score,String evaluate_content
            ,String orderNo,String mepNo,Integer courierId){

        Connection conn = null;
        try{
            conn = DBControl.getConnection();
            conn.setAutoCommit(false);
            String sql = "update cola_order set speed_score=?,goods_score=?,courier_score=?,evaluate_content=?,customer_evaluate_status=1 " +
                    "where order_no =?";
            boolean a = getQueryRunner().update(conn,sql,speed_score,goods_score,courier_score,evaluate_content,orderNo)>0;

            String sql2 = "update cola_courier set all_score = all_score+"+courier_score+" ,evaluate_num =evaluate_num+1 where courier_id =?";

            boolean b = getQueryRunner().update(conn,sql2,courierId)>0;

            conn.commit();
            return a&&b;
        }catch (Exception e){
            logger.error("配送评价异常！",e);
            if(conn!=null){
                try{
                    conn.rollback();
                }catch (Exception w){
                    logger.error("配送评价异常回滚异常！",e);
                }
            }

        }finally {
            DbUtils.closeQuietly(conn);
        }
        return false;
    }


    //===============================================================================

    public static void main(String[] args){
        System.out.println(JSON.toJSONString(getInstance().getGoodsList("23","Q004")));
    }

}
